package Dao;


import org.example.University;

import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class UniversityDao {
    public static UniversityDao universityDao =new UniversityDao();
    public static UniversityDao getUniversityDao() {
        return universityDao;
    }
    public University add(University university, Connection connection) throws SQLException {
        String addUniversity_sql = "INSERT INTO university ( id,descrption, no,remarks)VALUES"+"(?,?,?,?)";
        PreparedStatement stmt_add_all = connection.prepareStatement(addUniversity_sql);
        stmt_add_all.setInt(1,university.getId());
        stmt_add_all.setString(2,university.getDescription());
        stmt_add_all.setString(3,university.getNo());
        stmt_add_all.setString(4,university.getRemarks());
        stmt_add_all.executeUpdate();
        JdbcHelper.close(stmt_add_all,connection);
        //关闭connection对象。只要建立了连接，必须要关闭。
        return null;
    }
    

    public University upData(University university, Connection connection) throws SQLException {
        String updateUniversity_sql1 = "update university set descrption = ? , no = ? where id = ?";
        PreparedStatement stmt_update_name = connection.prepareStatement(updateUniversity_sql1);
        stmt_update_name.setString(1,university.getDescription());
        stmt_update_name.setString(2,university.getNo());
        stmt_update_name.setInt(3,university.getId());
        stmt_update_name.executeUpdate();
        JdbcHelper.close(stmt_update_name,connection);
        return null;
    }



    public Collection<University> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        Collection<University> desiredUniversitys = new HashSet<University>();
        //准备预编译的语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM University");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"university",conditionList);
        String pginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(pginationClause);
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet_select_all.next()) {
            String description = resultSet_select_all.getString("description");
            String remarks = resultSet_select_all.getString("remarks");
            String no = resultSet_select_all.getString("no");
           int id = resultSet_select_all.getInt("id");
            University university = new University(id,description,no,remarks);
            desiredUniversitys.add(university);
        }
        JdbcHelper.close(resultSet_select_all,pstmt_select_all,connection);
        return desiredUniversitys;
    }
    public University find(Integer id) throws SQLException {
        University desiredUniversity = null;
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM university WHERE id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setLong(1,id);
        ResultSet resultSet = pstmt_select_id.executeQuery();
        if (resultSet.next()){
            int id1 = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            desiredUniversity = new University(id1,description,no,remarks);
        }
        JdbcHelper.close(resultSet,pstmt_select_id,connection);
        return desiredUniversity;
    }
}
